options compress=YES;

libname movers ''; 
libname fra ''; 
libname fe '' ;

* MERGE LIFETIME EARNINGS and FEs INTO MOVERS SAMPLE TO ADDRESS RESTAT COMMENTS ;

* Prep lifetime earnings ;
data append_earn ;
	set 
		fra.mbrphusser02 (keep = ssn n_earnavg25to55)
		fra.mbrphusser03 (keep = ssn n_earnavg25to55)
		fra.mbrphusser05 (keep = ssn n_earnavg25to55)
		fra.mbrphusser06 (keep = ssn n_earnavg25to55)
		fra.mbrphusser09 (keep = ssn n_earnavg25to55)
		fra.mbrphusser10 (keep = ssn n_earnavg25to55)
		fra.mbrphusser15 (keep = ssn n_earnavg25to55)
		fra.mbrphusser16 (keep = ssn n_earnavg25to55)
		fra.mbrphusser19 (keep = ssn n_earnavg25to55)
		fra.mbrphusser20 (keep = ssn n_earnavg25to55)
	;
run ;

proc sort data=append_earn out=append_earn ;
	by ssn ;
run;


* Import movers file ;
proc import datafile = 'geo_movers.csv'
	out = movers.geo_movers
	dbms = csv
	replace ;
run ;

proc import datafile = 'geo_avg.csv'
	out = movers.geo_avg
	dbms = csv
	replace ;
run ;

proc import datafile = 'ein_movers.csv'
	out = movers.ein_movers
	dbms = csv
	replace ;
run ;

proc import datafile = 'ein_avg.csv'
	out = movers.ein_avg
	dbms = csv
	replace ;
run ;

%macro movers(unit) ;
data movers.&unit._movers_wearn ;
	set movers.&unit._movers 
	(rename = (ssn = ssn_num lag_&unit. = orgn_loc &unit. = dest_loc)) ;

	ssn = put(ssn_num, z9.) ;
	drop ssn_num ;
run ;

* Merge in lifetime earnings ;
data movers.&unit._movers_wearn ;

	merge

		movers.&unit._movers_wearn
			(in=from_mainx)

		append_earn
			(in=from_mbrx 
			keep = ssn n_earnavg25to55)
	; 

	by ssn ;
	if from_mainx=1 ;
	from_mbr=from_mbrx ;
run;

proc means data = movers.&unit._movers_wearn ;
	var from_mbr ;
run ;

proc sort data=movers.&unit._movers_wearn out=movers.&unit._movers_wearn ;
	by dest_loc ;
run;

%let DIR=;
proc export data=movers.&unit._movers_wearn
     file="&DIR\&unit._movers_wearn.dta"
     dbms=DTA REPLACE;
run;

proc export data=movers.&unit._avg
     file="&DIR\&unit._avg.dta"
     dbms=DTA REPLACE;
run;

%mend movers ;

%movers(geo) ;
%movers(ein) ;

run;
quit;


